﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;

namespace CallCenter.Data.Helper
{
    public class DB : System.IDisposable
    {
        private string ConnString;
        SqlConnection SqlConn = null;
        /// <summary>
        /// Constructor: Assign SQL Connection String to the member.
        /// </summary>
        public DB(string SqlConnectionString)
        {
            if (SqlConnectionString != "")
            {
                ConnString = SqlConnectionString;
                SqlConn = new SqlConnection(ConnString);
            }
        }
        /// <summary>
        /// Get a ConnectionString and asigned to the connection object.
        /// </summary>
        public String ConnectionString
        {
            set { SqlConn = new SqlConnection(value); }
        }

        private void GetParameters(SqlCommand SqlCmd, Hashtable SPParamList)
        {
            IDictionaryEnumerator myEnum = SPParamList.GetEnumerator();
            while (myEnum.MoveNext())
                SqlCmd.Parameters.AddWithValue("@" + myEnum.Key, myEnum.Value.ToString().Trim());
        }

        /// <summary>
        /// Method executes the stored procedure and returns the result in DataSet.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Stored Procedure results in DataSet</returns>
        public DataSet GetDataSetForSP(string SPName, Hashtable SPParams)
        {
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            GetParameters(SqlCmd, SPParams);
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataSet oDs = new DataSet();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 5000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDs);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return oDs;
        }

        public DataSet GetDataSetForSPWithParam(string SPName, SqlParameter[] SPParams = null)
        {
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            if (SPParams != null)
            {
                SqlCmd.Parameters.AddRange(SPParams);
            }
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataSet oDs = new DataSet();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 5000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDs);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return oDs;
        }


        /// <summary>
        /// Method executes the stored procedure and returns the result in DataTable.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Stored Procedure results in DataTable</returns>
        public DataTable GetDataTableForSP(string SPName, Hashtable SPParams)
        {
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            GetParameters(SqlCmd, SPParams);
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataTable oDt = new DataTable();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open)
                    SqlConn.Close();
            }
            return oDt;
        }




        /// <summary>
        /// Method executes the stored procedure and returns the result in DataTable.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Stored Procedure results in DataTable</returns>
        public DataTable GetDataTableForSPWithParam(string SPName, SqlParameter[] SPParams = null)
        {
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;

            //For optional params
            if (SPParams != null)
            {
                SqlCmd.Parameters.AddRange(SPParams);
            }
            //GetParameters(SqlCmd, SPParams);
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataTable oDt = new DataTable();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open)
                    SqlConn.Close();
            }
            return oDt;
        }
        public int GetIntResultForSPWithParamOut(string SPName, SqlParameter[] SPParams)
        {
            int lStr;
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.AddRange(SPParams);
            //GetParameters(SqlCmd, SPParams);
            //SqlParameter param = SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
            //param.Direction = ParameterDirection.ReturnValue;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                lStr = SqlCmd.ExecuteNonQuery();
                //lStr = Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value);
            }
            catch (SqlException error)
            {
                return (Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value) != error.Number && SqlCmd.Parameters["@ReturnValue"].Value != null) ? Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value) : error.Number;
            }
            catch (Exception e)
            {
                string msg = e.Message;
                return -1;
            }

            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return lStr;
        }

        public int GetIntResultForSPWithParam(string SPName, SqlParameter[] SPParams)
        {
            int lStr;
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.AddRange(SPParams);
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                lStr = SqlCmd.ExecuteNonQuery();
            }
            catch (SqlException error)
            {
                throw;
            }
            catch (Exception e)
            {
                string msg = e.Message;
                return -1;
            }

            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return lStr;
        }

        public DataTable GetDataTableForSPWithoutParam(string SPName)
        {
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;

            //GetParameters(SqlCmd, SPParams);
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataTable oDt = new DataTable();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open)
                    SqlConn.Close();
            }
            return oDt;
        }
        /// <summary>
        /// Method executes the SQL Query and returns the result in DataTable.
        /// </summary>
        /// <param name="SqlQuery">SQL Statement.</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableForQuery(string SqlQuery)
        {
            if (SqlQuery == "") return null;
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
            SqlCmd.CommandType = CommandType.Text;
            SqlDataAdapter oDa = new SqlDataAdapter();
            DataTable oDt = new DataTable();
            oDa.SelectCommand = SqlCmd;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                oDa.Fill(oDt);
            }
            catch
            {
                throw;
                //return oDs;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return oDt;
        }

        /// <summary>
        /// Method executes the stored procedure and returns the value return by stored procedure as string.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Returns the value return by Stored Procedure as string</returns>
        public string GetStringResultForSP(string SPName, Hashtable SPParams)
        {
            string lStr = string.Empty;
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            GetParameters(SqlCmd, SPParams);
            SqlParameter param = SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
            param.Direction = ParameterDirection.ReturnValue;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                lStr = SqlCmd.Parameters["@ReturnValue"].Value.ToString();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return lStr;
        }
        /// <summary>
        /// Method executes the stored procedure and returns the value return by stored procedure as integer.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Returns the value return by Stored Procedure as integer</returns>
        public int GetIntResultForSP(string SPName, Hashtable SPParams)
        {
            int lStr;
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            GetParameters(SqlCmd, SPParams);
            SqlParameter param = SqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
            param.Direction = ParameterDirection.ReturnValue;
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                SqlCmd.ExecuteNonQuery();
                lStr = Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value);
            }
            catch (SqlException error)
            {
                return (Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value) != error.Number && SqlCmd.Parameters["@ReturnValue"].Value != null) ? Convert.ToInt32(SqlCmd.Parameters["@ReturnValue"].Value) : error.Number;
            }
            catch (Exception e)
            {
                string msg = e.Message;
                return -1;
            }

            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return lStr;
        }

        /// <summary>
        /// Method executes the stored procedure and returns the XML string.
        /// </summary>
        /// <param name="SPName">Stored Procedure Name</param>
        /// <param name="SPParams">Parameter list for stored procedure</param>
        /// <returns>Returns the XML string</returns>
        public string GetXMLForSP(string SPName, Hashtable SPParams)
        {
            StringBuilder lStr = new StringBuilder();
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            GetParameters(SqlCmd, SPParams);
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                XmlReader xRead = SqlCmd.ExecuteXmlReader();
                xRead.Read();
                while (xRead.ReadState != ReadState.EndOfFile)
                {
                    lStr.Append(xRead.ReadOuterXml());
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return "<root>" + lStr.ToString() + "</root>";

        }




        public string GetXMLForSPWithParam(string SPName, SqlParameter[] SPParams)
        {
            StringBuilder lStr = new StringBuilder();
            //SqlConnection SqlConn = new SqlConnection(ConnString);
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.AddRange(SPParams);
            //GetParameters(SqlCmd, SPParams);
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                XmlReader xRead = SqlCmd.ExecuteXmlReader();
                xRead.Read();
                while (xRead.ReadState != ReadState.EndOfFile)
                {
                    lStr.Append(xRead.ReadOuterXml());
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
            return lStr.ToString();

        }

        /// <summary>
        /// Method executes the stored procedure and returns the XML string.
        /// </summary>
        /// <param name="tableName">Table Name</param>
        /// <param name="columnName">Column Name</param>
        /// <param name="value">value</param>
        /// <returns>Returns bool</returns>
        public int CheckDuplicateExistsWithParamUsingScalar(string SPName, SqlParameter[] SPParams)
        {
            SqlCommand SqlCmd = new SqlCommand(SPName, SqlConn);
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.Parameters.AddRange(SPParams);
            SqlCmd.CommandTimeout = 50000000;
            try
            {
                SqlConn.Open();
                return (int)SqlCmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                string msg = e.Message;
                return -1;
            }
            finally
            {
                if (SqlConn.State == ConnectionState.Open) SqlConn.Close();
            }
        }


        #region IDisposable Members
        /// <summary>
        /// Dispose implementation.
        /// Method to clear resources pertaining to connection
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool pBoolDispose)
        {
            if (pBoolDispose)
            {
                if (SqlConn != null) SqlConn.Close();
            }
        }

        #endregion
    }
}